﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Cocon90.Lib.Dal.Tools
{
    /// <summary>
    /// 实体类辅助类
    /// </summary>
    /// <typeparam name="T">此类型必须要有一个无参的构造函数，且实现IModel</typeparam>
    public class ModelHelper<T> where T : class,Rule.IModel, new()
    {
        public ModelHelper<T> CreateInstence()
        {
            return new ModelHelper<T>();
        }

        /// <summary>
        /// 由传入的Sql语句来查询出对应的List
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<T> GetList(string tsqlParamed, params System.Data.Common.DbParameter[] paramKeyValue)
        {
            var db = DataHelperFactory.CreateInstence();
            return GetList(db.getTable(tsqlParamed, paramKeyValue));
        }
        /// <summary>
        /// 由DataTable返回此类型模型集合
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<T> GetList(System.Data.DataTable table)
        {
            DataModel<T> dm = new DataModel<T>();
            return dm.GetList(table);
        }
        /// <summary>
        /// 取得单个模型，如果无记录，则反回null
        /// </summary>
        /// <param name="tsqlParamed"></param>
        /// <param name="paramKeyValue"></param>
        /// <returns></returns>
        public T GetOne(string tsqlParamed, params System.Data.Common.DbParameter[] paramKeyValue)
        {
            var lst = GetList(tsqlParamed, paramKeyValue);
            if (lst == null || lst.Count <= 0) return null;
            else return lst[0];
        }
        /// <summary>
        /// 取得单个模型，如果无记录，则反回null
        /// </summary>
        /// <param name="tsqlParamed"></param>
        /// <param name="paramKeyValue"></param>
        /// <returns></returns>
        public T GetOneByPrimeryKey(object paramKeyValue)
        {
            string primeryKey = GetPrimeryKey();
            string tsqlParamed = String.Format("select * from {0} where {1}=@{1}", GetTableName(), primeryKey);
            var db = DataHelperFactory.CreateInstence();
            var lst = GetList(tsqlParamed, db.createParameter("@" + primeryKey, paramKeyValue));
            if (lst == null || lst.Count <= 0) return null;
            else return lst[0];
        }
        /// <summary>
        /// 取得表中第一行，作为单个模型，如果无记录，则反回null
        /// </summary>
        /// <param name="tsqlParamed"></param>
        /// <param name="paramKeyValue"></param>
        /// <returns></returns>
        public T GetOne(System.Data.DataTable table)
        {
            var lst = GetList(table);
            if (lst == null || lst.Count <= 0) return null;
            else return lst[0];
        }
        /// <summary>
        /// 取得当前Model的主键列名
        /// </summary>
        /// <returns></returns>
        public string GetPrimeryKey()
        {
            var db = DataHelperFactory.CreateInstence();
            var model = (T)Activator.CreateInstance(typeof(T));
            var primeryKey = db.getString(sqlHelper.getPrimeryKey(model.TableName));
            return primeryKey;
        }
        /// <summary>
        /// 取得指定Model类型对应的表主键列名
        /// </summary>
        /// <param name="modelType"></param>
        /// <returns></returns>
        public string GetPrimeryKey(Type modelType)
        {
            var db = DataHelperFactory.CreateInstence();
            var model = (T)Activator.CreateInstance(modelType);
            var primeryKey = db.getString(sqlHelper.getPrimeryKey(model.TableName));
            return primeryKey;
        }
        /// <summary>
        /// 取得Model类型的表名
        /// </summary>
        /// <returns></returns>
        public string GetTableName()
        {
            var model = (T)Activator.CreateInstance(typeof(T));
            return model.TableName;
        }
        /// <summary>
        /// 取得指定的Model类型的表名
        /// </summary>
        /// <returns></returns>
        public string GetTableName(Type modelType)
        {
            var model = (T)Activator.CreateInstance(modelType);
            return model.TableName;
        }
        /// <summary>
        /// 取得记录总数，可以添加自定义Where子句，不需包含where单词
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetRowCount(string customWhere = null)
        {
            var db = DataHelperFactory.CreateInstence();
            var model = (T)Activator.CreateInstance(typeof(T));
            var primeryKey = db.getString(sqlHelper.getPrimeryKey(model.TableName));
            if (customWhere == null)
                return db.getNumber(String.Format("select count({0}) from {1} ", primeryKey, model.TableName));
            else
                return db.getNumber(String.Format("select count({0}) from {1} where {2}", primeryKey, model.TableName, customWhere));
        }
        /// <summary>
        /// 判断指定类型中是否包含指定名称的属性。
        /// </summary>
        public bool ContainsProperty(Type type, string PropertyName)
        {
            foreach (var item in type.GetProperties())
            {
                if (item.Name == PropertyName)
                {
                    return true;
                }
            }
            return false;
        }
        /// <summary>
        ///将Model实体映射成List集合。可以指定是否把Model中值为NULL的属性也映射进去。
        /// </summary>
        /// <param name="model"></param>
        /// <param name="isContainNullValue"></param>
        /// <returns></returns>
        public List<string> GetColumnNames(T model, bool isContainNullValue)
        {
            List<string> columnsNames = new List<string>();
            var mType = typeof(T);
            foreach (var prop in mType.GetProperties())
            {
                if (ContainsProperty(typeof(Rule.IModel), prop.Name)) { continue; }//过滤掉Rule.IModel中包含的属性
                if (isContainNullValue)
                {
                    columnsNames.Add(prop.Name);
                }
                else
                {
                    if (prop.GetValue(model, null) != null)//如果Model中的某个属性值不为NULL
                    {
                        columnsNames.Add(prop.Name);
                    }
                }

            }
            return columnsNames;
        }
        /// <summary>
        /// 添加记录（添加IModel类型实体，如果某项属性不想添加，或者使用默认值，请将该列设置为NULL或者使用默认的可空类型）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(T model)
        {
            List<string> columnsNames = GetColumnNames(model, false);
            var sqlParam = sqlHelper.getInsertSqlParam(model.TableName, columnsNames);//生成包含@Col1,@Col2等带参的SQL语句
            var db = DataHelperFactory.CreateInstence();
            List<System.Data.Common.DbParameter> dbParams = new List<System.Data.Common.DbParameter>();
            foreach (var colName in columnsNames)
            {
                dbParams.Add(db.createParameter(String.Format("@{0}", colName), typeof(T).GetProperty(colName).GetValue(model, null)));
            }
            return db.execNoQuery(sqlParam, dbParams.ToArray()) > 0;
        }
        /// <summary>
        /// 添加记录（添加IModel类型实体，如果某项属性不想添加，或者使用默认值，请将该列设置为NULL或者使用默认的可空类型） GeneratedKey返回自增长的主键值，出现异常时反回-1
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(T model, out long GeneratedKey)
        {
            try
            {
                List<string> columnsNames = GetColumnNames(model, false);
                var sqlParam = sqlHelper.getInsertSqlParam(model.TableName, columnsNames);//生成包含@Col1,@Col2等带参的SQL语句
                var db = DataHelperFactory.CreateInstence();
                List<System.Data.Common.DbParameter> dbParams = new List<System.Data.Common.DbParameter>();
                foreach (var colName in columnsNames)
                {
                    dbParams.Add(db.createParameter(String.Format("@{0}", colName), typeof(T).GetProperty(colName).GetValue(model, null)));
                }
                GeneratedKey = long.Parse("0" + db.getString(sqlParam + ";select @@IDENTITY ;", dbParams.ToArray()));
                return true;
            }
            catch { GeneratedKey = -1; return false; }
        }
        /// <summary>
        /// 修改记录，需传入该表主键的值（修改IModel类型实体，如果某项属性不想修改，或者使用默认值，请将该列设置为NULL或者使用默认的可空类型）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool EditByPrimeryKey(T model, object primeryKeyValue, bool isNullMeansNotChange = true)
        {

            var db = DataHelperFactory.CreateInstence();
            var primeryKey = db.getString(sqlHelper.getPrimeryKey(model.TableName));
            List<string> columnsNames = null;
            if (isNullMeansNotChange) { columnsNames = GetColumnNames(model, false); }//如果Null还未不改变该列
            else
            {
                columnsNames = GetColumnNames(model, true);
                columnsNames.Remove(primeryKey);
            }

            var keyValuePara = Guid.NewGuid().ToString("N");
            var where = String.Format("{0}=@{1}", primeryKey, keyValuePara);
            var sqlParam = sqlHelper.getUpdateSqlParam(model.TableName, columnsNames, where);//生成包含@Col1,@Col2等带参的SQL语句
            List<System.Data.Common.DbParameter> dbParams = new List<System.Data.Common.DbParameter>();
            dbParams.Add(db.createParameter("@" + keyValuePara, primeryKeyValue));//添加主键查询Where子句中的变量
            foreach (var colName in columnsNames)
            {
                if (isNullMeansNotChange)
                {
                    dbParams.Add(db.createParameter(String.Format("@{0}", colName), typeof(T).GetProperty(colName).GetValue(model, null)));
                }
                else
                {
                    var value = typeof(T).GetProperty(colName).GetValue(model, null);
                    if (value == null)
                    { dbParams.Add(db.createParameter(String.Format("@{0}", colName), DBNull.Value)); }
                    else { dbParams.Add(db.createParameter(String.Format("@{0}", colName), value)); }
                }
            }
            return db.execNoQuery(sqlParam, dbParams.ToArray()) > 0;
        }
        /// <summary>
        /// 修改记录，需传入自定义Where子句(不包括"where单词")，若没有Where请传NULL值（修改IModel类型实体，如果某项属性不想修改，或者使用默认值，请将该列设置为NULL或者使用默认的可空类型）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool EditByCustomWhere(T model, string customWhereStirng, bool isNullMeansNotChange = true, params System.Data.Common.DbParameter[] whereParas)
        {
            
            var db = DataHelperFactory.CreateInstence();
            List<string> columnsNames = null;
            if (isNullMeansNotChange) { columnsNames = GetColumnNames(model, false); }//如果Null还未不改变该列
            else
            {
                columnsNames = GetColumnNames(model, true);
                var primeryKey = db.getString(sqlHelper.getPrimeryKey(model.TableName));
                columnsNames.Remove(primeryKey);
            }

            var where = customWhereStirng;
            var sqlParam = sqlHelper.getUpdateSqlParam(model.TableName, columnsNames, where);//生成包含@Col1,@Col2等带参的SQL语句
            List<System.Data.Common.DbParameter> dbParams = new List<System.Data.Common.DbParameter>();
            if (whereParas != null)
            {
                foreach (var whereParaItem in whereParas)
                {
                    dbParams.Add(whereParaItem);
                }
            }
            foreach (var colName in columnsNames)
            {
                if (isNullMeansNotChange)
                {
                    dbParams.Add(db.createParameter(String.Format("@{0}", colName), typeof(T).GetProperty(colName).GetValue(model, null)));
                }
                else
                {
                    var value = typeof(T).GetProperty(colName).GetValue(model, null);
                    if (value == null)
                    { dbParams.Add(db.createParameter(String.Format("@{0}", colName), DBNull.Value)); }
                    else { dbParams.Add(db.createParameter(String.Format("@{0}", colName), value)); }
                }
            }
            return db.execNoQuery(sqlParam, dbParams.ToArray()) > 0;
        }
        /// <summary>
        /// 删除记录，传入主键值。
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DeleteByPrimeryKey(object primeryKeyValue)
        {
            var db = DataHelperFactory.CreateInstence();
            var model = (T)Activator.CreateInstance(typeof(T));
            var primeryKey = db.getString(sqlHelper.getPrimeryKey(model.TableName));
            Dictionary<string, object> wheredic = new Dictionary<string, object>();
            wheredic.Add(primeryKey, primeryKeyValue);
            var sql = sqlHelper.getDeleteSqlParam(model.TableName, wheredic);
            return db.execNoQuery(sql) > 0;
        }
        /// <summary>
        /// 删除记录，删除IModel类型实体，除主键之处全传入null即可。
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DeleteByModel(T model)
        {
            var db = DataHelperFactory.CreateInstence();
            var primeryKey = db.getString(sqlHelper.getPrimeryKey(model.TableName));
            return DeleteByPrimeryKey(typeof(T).GetProperty(primeryKey).GetValue(model, null));
        }
        /// <summary>
        /// 删除记录，传入自定义Where子句（不包括where单词）
        /// </summary>
        /// <returns></returns>
        public bool DeleteByCustomWhere(string customWhereStirng, params System.Data.Common.DbParameter[] whereParas)
        {
            var db = DataHelperFactory.CreateInstence();
            var model = (T)Activator.CreateInstance(typeof(T));
            var sql = sqlHelper.getDeleteSql(model.TableName, customWhereStirng);
            if (whereParas != null) return db.execNoQuery(sql, whereParas) > 0;
            return db.execNoQuery(sql) > 0;
        }
        /// <summary>
        /// 判断两个模型的值 是否一一对应相等。
        /// </summary>
        /// <param name="oldModel"></param>
        /// <param name="newModel"></param>
        /// <returns></returns>
        public bool IsModelEqual(T oldModel, T newModel)
        {
            var type = typeof(T);
            var properties = type.GetProperties();
            foreach (var prop in properties)
            {
                var oldPropValue = prop.GetValue(oldModel, null);
                var newPropValue = prop.GetValue(newModel, null);
                if (oldPropValue + "" != newPropValue + "")
                {
                    return false;
                }
            }
            return true;
        }

    }
}
